{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "<b>Prepare Christmas cards with Gemini and Sheets</b>\n",
        "\n",
        "Do you want a little help thinking of something personal to say on your Christmas cards? Obviously you shouldn't outsource everything, but maybe Gemini can help you think of what to say to the dozens of people you connect with."
      ],
      "metadata": {
        "id": "ZWhWniBGu3_Y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title Authenticate and grant Colab access to your spreadsheets for this session\n",
        "\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "\n",
        "import gspread\n",
        "from google.auth import default\n",
        "creds, _ = default()\n",
        "\n",
        "gc = gspread.authorize(creds)"
      ],
      "metadata": {
        "id": "_oO7-MlMpWd2",
        "cellView": "form"
      },
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@title Configure Gemini API key\n",
        "\n",
        "#Access your Gemini API key\n",
        "\n",
        "import google.generativeai as genai\n",
        "from google.colab import userdata\n",
        "\n",
        "gemini_api_secret_name = 'GOOGLE_API_KEY'  # @param {type: \"string\"}\n",
        "\n",
        "try:\n",
        "  GOOGLE_API_KEY=userdata.get(gemini_api_secret_name)\n",
        "  genai.configure(api_key=GOOGLE_API_KEY)\n",
        "except userdata.SecretNotFoundError as e:\n",
        "   print(f'''Secret not found\n\nThis expects you to create a secret named {gemini_api_secret_name} in Colab\n\nVisit https://makersuite.google.com/app/apikey to create an API key\n\nStore that in the secrets section on the left side of the notebook (key icon)\n\nName the secret {gemini_api_secret_name}''')\n",
        "   raise e\n",
        "except userdata.NotebookAccessError as e:\n",
        "  print(f'''You need to grant this notebook access to the {gemini_api_secret_name} secret in order for the notebook to access Gemini on your behalf.''')\n",
        "  raise e\n",
        "except Exception as e:\n",
        "  # unknown error\n",
        "  print(f\"There was an unknown error. Ensure you have a secret {gemini_api_secret_name} stored in Colab and it's a valid key from https://makersuite.google.com/app/apikey\")\n",
        "  raise e\n",
        "\n",
        "model = genai.GenerativeModel('gemini-pro')"
      ],
      "metadata": {
        "id": "yFv1abRcv2P2",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Create a spreadsheet\n",
        "\n",
        "Create a new spreadsheet at [sheets.new](https://sheets.new) with the data Gemini needs: name, relationship, and (optionally) style of how you want to address that person (funny, tender, sincere, thankful, [blank inside](https://www.youtube.com/watch?v=auh_6pSwjHE), really any attribute you want.).\n",
        "\n",
        "For this example we've made a small test spreadsheet for you that you can use."
      ],
      "metadata": {
        "id": "UoAAnPPPSXNn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title Enter the name of a spreadsheet to import\n",
        "\n",
        "spreadsheet_url = \"https://docs.google.com/spreadsheets/d/1ZX5Q3BvhOegE33LaIA_zETouvJdQE7AgflkF6lyriCU/edit?usp=sharing\" #@param {type:\"string\"}\n",
        "\n",
        "worksheet = gc.open_by_url(spreadsheet_url).sheet1\n",
        "\n",
        "# get_all_values gives a list of rows.\n",
        "rows = worksheet.get_all_values()\n",
        "print(rows[0:5])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "cellView": "form",
        "id": "gGnYhGtfyb8c",
        "outputId": "5032b8b9-0249-442a-e0ac-8dcb89bbe415"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[['Name', 'Relationship', 'Style'], ['Jeremiah', 'Brother', 'Funny'], ['Mom', 'Mom', 'Sincere'], ['Brittney', 'Wife', 'Tender']]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@title Use Gemini to suggest what to say on a Christmas note\n",
        "\n",
        "rows[0].append('Note')\n",
        "\n",
        "#skip the title row\n",
        "for row in rows[1:]:\n",
        "  prompt = \"I'm writing Christmas cards. Can you suggest a short meaningful paragraph of text for a Christmas note? I'm writing a note to %s my %s and I'd like the style to be %s.\" % (row[0], row[1], row[2])\n",
        "\n",
        "  response = model.generate_content(prompt)\n",
        "  row.append(response.text)\n",
        "\n",
        "#print out the results nicely\n",
        "from IPython.display import Markdown\n",
        "import textwrap\n",
        "\n",
        "def to_markdown(text):\n",
        "  text = text.replace('•', '  *')\n",
        "  return Markdown(textwrap.indent(text, '> ', predicate=lambda _: True))\n",
        "\n",
        "to_markdown(''.join([row[3]+'\\n\\n' for row in rows[1:]]))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 213
        },
        "cellView": "form",
        "id": "KuvZs9W1USEq",
        "outputId": "91965dbe-7e89-4f93-93fa-857425b608d7"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ],
            "text/markdown": "> Jeremiah, you're like the 'R' in reindeer—without you, everything would be 'ein'deer! Let's sprinkle some gingerbread joy and have a holly jolly Christmas. Whatever you do, don't get 'elfed' up! Wishing you a season filled with warm hugs and mistletoes.\n> \n> Merry Christmas, Mom! As we approach this special holiday, I want to pause and express my sincere gratitude for everything you've done for me. Your unwavering love, sacrificial spirit, and guidance have shaped me into the person I am today. Your presence in my life is a constant source of comfort, encouragement, and inspiration. May our home be filled with the warmth of love, laughter, and peace as we celebrate this joyous occasion. From the bottom of my heart, I wish you a Christmas filled with memories that will last a lifetime. Love, [Your Name]\n> \n> To my loving wife Brittney, this festive season brings a glow in our hearts as we cherish the spirit of Christmas together. May the warmth of this holiday fill our home with love, laughter, and unforgettable moments. As we gather around the tree, let the twinkling lights and ornaments remind us of the joy and magic that this time of year brings. Merry Christmas, my dearest Brittney. I love you more than words can say.\n> \n"
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#(OPTIONAL) Write the notes back to your spreadsheet\n",
        "\n",
        "This only works for a spreadsheet you own, not the example sheet!"
      ],
      "metadata": {
        "id": "0GfT_TIj0b_p"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title Write the notes back to your spreadsheet\n",
        "\n",
        "#this chooses which column to write to\n",
        "col = len(rows[0])\n",
        "start_cell = chr(col + 64) + '1'\n",
        "\n",
        "#write the column\n",
        "try:\n",
        "  worksheet.update(start_cell, [[row[3]] for row in rows])\n",
        "except Exception as e:\n",
        "  print('There was an error writing to your spreadsheet. Make sure you have permissions to edit the spreadsheet.')\n",
        "  raise e"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "cellView": "form",
        "id": "-jc_4i12Ys3C",
        "outputId": "53e93451-5de6-4ff3-f033-1c115cce086a"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '1ZX5Q3BvhOegE33LaIA_zETouvJdQE7AgflkF6lyriCU',\n",
              " 'updatedRange': 'Sheet1!D1:D4',\n",
              " 'updatedRows': 4,\n",
              " 'updatedColumns': 1,\n",
              " 'updatedCells': 4}"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#The notes are in!\n",
        "\n",
        "Go check your spreadsheet in Drive - there's a new column with the notes Gemini generated. Happy Holidays!"
      ],
      "metadata": {
        "id": "FB419aaKeMFt"
      }
    }
  ]
}